SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted1	NUMBER;
	l_total_imported1	NUMBER;				
	l_total_extracted2	NUMBER;
	l_total_imported2	NUMBER;				
	l_total_extracted3	NUMBER;
	l_total_imported3	NUMBER;				
	l_func_id			VARCHAR2(100):='CAPFNDREC_RESP';

BEGIN
	SELECT COUNT(1)
	INTO l_total_extracted1  
	FROM
		FND_RESPONSIBILITY		FR,
		FND_RESPONSIBILITY_TL	FRTL,
		FND_DATA_GROUPS			FDG,
		FND_REQUEST_GROUPS		FRG,
		FND_MENUS				FM
	WHERE	FRTL.Responsibility_Name	LIKE	UPPER('&1')||'%'
	AND		FR.Responsibility_id	=		FRTL.Responsibility_id
	AND		FRTL.Language			=		'US'
	AND		FR.Menu_ID				=		FM.Menu_ID
	AND		FR.Data_Group_ID		=		FDG.Data_Group_ID
	AND		FR.Group_Application_ID	=		FRG.Application_ID(+)
	AND		FR.Request_Group_ID		=		FRG.Request_Group_ID(+);
	
	SELECT COUNT(1)
	INTO l_total_extracted2  
	FROM
		FND_RESPONSIBILITY_TL
		WHERE	Responsibility_ID	IN	(
				SELECT
					A.Responsibility_ID
				FROM
					FND_RESPONSIBILITY_TL	A
				WHERE	A.Language = 'US'
				AND		A.Responsibility_Name	LIKE UPPER('&1')||'%'
				);
	
	SELECT COUNT(1)
	INTO l_total_extracted3  
	FROM
		FND_RESP_FUNCTIONS		FRF,
		FND_RESPONSIBILITY_VL	FRVL
	WHERE	FRVL.Responsibility_Name	LIKE	UPPER('&1')||'%'
	AND		FRVL.Responsibility_ID		=		FRF.Responsibility_ID;

	SELECT COUNT(1) 
	INTO l_total_imported1
	FROM CRST_FND_RESPONSIBILITY;
	
	SELECT COUNT(1) 
	INTO l_total_imported2
	FROM CRST_FND_RESPONSIBILITY_TL;
	
	SELECT COUNT(1) 
	INTO l_total_imported3
	FROM CRST_FND_RESP_FUNCTIONS;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted1)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported1));
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted2)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported2));
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted3)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported3));
	IF l_total_extracted1 <> l_total_imported1 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	ELSE 
		IF l_total_extracted2 <> l_total_imported2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
		ELSE 
			IF l_total_extracted3 <> l_total_imported3 THEN
			dbms_output.put_line('Error: No. of Records do not match!');
			END IF;
		END IF;
	END IF;		
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
